package br.com.caelum.jdbc.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.caelum.ConnectionFactory;
import br.com.caelum.Funcionario;

import com.mysql.jdbc.PreparedStatement;

public class FuncionarioDAO {
	
	private Connection connection;
	
	public FuncionarioDAO(){
		this.connection = new ConnectionFactory().getConnection();
	}
	
	//insert
	public void adiciona(Funcionario funcionario){
		
		//query
		String sql = "insert into funcionarios" + "(nome, usuario, senha)" + "values(?,?,?)";
		
		try{
			//prepared statement para inserção
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
			
			//Seta os valores
			stmt.setString(1, funcionario.getNome());
			stmt.setString(2, funcionario.getUsuario());
			stmt.setString(3, funcionario.getSenha());
			
			//executa
			stmt.execute();
			stmt.close();
			
		}catch(SQLException e){
			throw new RuntimeException(e);
		}
	}
	
	//atualiza
	public void altera(Funcionario funcionario){
		
		//query
		String sql = "update funcionarios set nome=?, usuario=?, senha=?," + "where id=?";

		
		try{
			//prepared statement para atualização
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
			
			//Seta os valores
			stmt.setString(1, funcionario.getNome());
			stmt.setString(2, funcionario.getUsuario());
			stmt.setString(3, funcionario.getSenha());
			stmt.setLong(4, funcionario.getId());

			
			//executa
			stmt.execute();
			stmt.close();
			
		}catch(SQLException e){
			throw new RuntimeException(e);
		}
	}
		
	//remove
	public void remove(Funcionario funcionario){
		
		//query
		String sql = "delete from funcionarios where id=?";
		
		try{
			//prepared statement para remoção
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
			
			//Seta os valores
			stmt.setLong(1, funcionario.getId());
			
			//executa
			stmt.execute();
			stmt.close();
			
		}catch(SQLException e){
			throw new RuntimeException(e);
		}
	}
	
	//lista
	public List<Funcionario> getLista(){
		
		try{
			List<Funcionario> funcionarios = new ArrayList<Funcionario>();
			PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from funcionarios");
			ResultSet rs = stmt.executeQuery();
			
			while(rs.next()){
				//criando o contato
				Funcionario funcionario = new Funcionario();
				funcionario.setId(rs.getLong("id"));
				funcionario.setNome(rs.getString("nome"));
				funcionario.setUsuario(rs.getString("usuario"));
				funcionario.setSenha(rs.getString("senha"));
				
				//adicionando o objeto a lista
				funcionarios.add(funcionario);
			}
			
			rs.close();
			stmt.close();
			
			return funcionarios;
			
		}catch(SQLException e){
			throw new RuntimeException(e);

		}
		
		
	}
}
